using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApp_xls2xlsx
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog openFileDialog = new OpenFileDialog())
            {
                openFileDialog.Filter = "Excel Files (*.xls)|*.xls";
                openFileDialog.Title = "选择要转换的XLS文件";

                if (openFileDialog.ShowDialog() == DialogResult.OK)
                {
                    string xlsFilePath = openFileDialog.FileName;
                    string xlsxFilePath = Path.ChangeExtension(xlsFilePath, ".xlsx");

                    try
                    {
                        ConvertXlsToXlsx(xlsFilePath, xlsxFilePath);
                        MessageBox.Show($"文件已成功转换为XLSX格式并保存到:\n{xlsxFilePath}", "转换成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show($"转换过程中发生错误: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
        }

        /// <summary>
        /// 将XLS文件转换为XLSX格式
        /// </summary>
        /// <param name="xlsFilePath">源XLS文件路径</param>
        /// <param name="xlsxFilePath">目标XLSX文件路径</param>
        private void ConvertXlsToXlsx(string xlsFilePath, string xlsxFilePath)
        {
            try
            {
                // 使用System.Data.OleDb读取XLS文件
                string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={xlsFilePath};Extended Properties='Excel 8.0;HDR=YES;'";
                using (OleDbConnection connection = new OleDbConnection(connectionString))
                {
                    try
                    {
                        connection.Open();
                        Console.WriteLine($"成功连接到Excel文件: {xlsFilePath}");

                        // 获取所有工作表
                        DataTable schemasTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                        if (schemasTable == null || schemasTable.Rows.Count == 0)
                        {
                            throw new Exception("无法获取Excel工作表信息，文件可能为空或格式不正确。");
                        }

                        Console.WriteLine($"找到 {schemasTable.Rows.Count} 个工作表");

                        // 创建新的XLSX工作簿
                        using (var workbook = new XLWorkbook())
                        {
                            foreach (DataRow schemaRow in schemasTable.Rows)
                            {
                                string sheetName = schemaRow["TABLE_NAME"].ToString();
                                
                                // 移除工作表名称中的引号和$符号
                                sheetName = sheetName.Replace("'", "").Replace("$", "");
                                Console.WriteLine($"处理工作表: {sheetName}");

                                // 读取工作表数据
                                string query = $"SELECT * FROM [{schemaRow["TABLE_NAME"]}]";
                                using (OleDbCommand command = new OleDbCommand(query, connection))
                                {
                                    using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                                    {
                                        DataTable dataTable = new DataTable();
                                        adapter.Fill(dataTable);

                                        Console.WriteLine($"工作表 {sheetName} 包含 {dataTable.Rows.Count} 行和 {dataTable.Columns.Count} 列");

                                        // 创建新的工作表
                                        var worksheet = workbook.Worksheets.Add(sheetName);

                                        // 写入列标题
                                        for (int i = 0; i < dataTable.Columns.Count; i++)
                                        {
                                            worksheet.Cell(1, i + 1).Value = dataTable.Columns[i].ColumnName;
                                        }

                                        // 写入数据
                                        for (int row = 0; row < dataTable.Rows.Count; row++)
                                        {
                                            for (int col = 0; col < dataTable.Columns.Count; col++)
                                            {
                                                // 处理可能的空值
                                                var cellValue = dataTable.Rows[row][col];
                                                if (cellValue != DBNull.Value)
                                                {
                                                    worksheet.Cell(row + 2, col + 1).Value = cellValue.ToString();
                                                }
                                            }
                                        }

                                        // 自动调整列宽
                                        worksheet.Columns().AdjustToContents();
                                    }
                                }
                            }

                            // 保存为XLSX文件
                            Console.WriteLine($"正在保存XLSX文件: {xlsxFilePath}");
                            workbook.SaveAs(xlsxFilePath);
                            Console.WriteLine("文件保存成功");
                        }
                    }
                    catch (OleDbException ex)
                    {
                        Console.WriteLine($"OleDb错误: {ex.Message}");
                        throw new Exception($"读取Excel文件时出错: {ex.Message}", ex);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"转换过程中发生错误: {ex.Message}");
                if (ex.InnerException != null)
                {
                    Console.WriteLine($"内部错误: {ex.InnerException.Message}");
                }
                throw; // 重新抛出异常以便调用者处理
            }
        }
    }
}
